package vesko1.model;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import javax.activation.DataHandler;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.util.ByteArrayDataSource;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/**
 *
 * @author Vesko
 */
public class InsuranceDAO {

    public static int addNewInsurance(Insurance ins, String scannedDocumentPath) {
        try {
            Connection con = DBConnection.getCon();
            FileInputStream fis = null;
            if (scannedDocumentPath != null && scannedDocumentPath.length() > 0) {
                fis = new FileInputStream(scannedDocumentPath);
            }

            PreparedStatement stmt = con.prepareStatement("INSERT INTO insurance values(DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            stmt.setInt(1, ins.getNumberOfPayments());
            stmt.setDate(2, new java.sql.Date(ins.getDateIssued().getTime()));
            stmt.setBoolean(3, false);
            stmt.setString(4, ins.getCarNumber());
            stmt.setString(5, ins.getCompany());
            stmt.setString(6, ins.getPolicy());
            stmt.setDate(7, new java.sql.Date(ins.getValidFrom().getTime()));
            stmt.setDate(8, new java.sql.Date(ins.getValidTo().getTime()));
            stmt.setString(9, ins.getGreenCardNumber());
            stmt.setFloat(10, ins.getPremia());
            stmt.setFloat(11, ins.getGrajdanskiFond());
            stmt.setString(12, ins.getStickerNumber());
            stmt.setBinaryStream(13, fis);

            stmt.executeUpdate();

            stmt.close();
            Statement st = DBConnection.getCon().createStatement();
            st.execute("SHOW TABLE STATUS LIKE 'insurance';");
            ResultSet set = st.getResultSet();
            set.next();
            int id = set.getInt("Auto_increment") - 1;

            System.out.println("1 new insurance inserted with id " + id);
            return id;
        } catch (FileNotFoundException | SQLException ex) {
            ex.printStackTrace();
            return -1;
        }
    }

    public static void saveInstallments(Installments installments) {
        try {
            Connection con = DBConnection.getCon();
            for (int i = 0; i < installments.getDates().length; i++) {
                PreparedStatement stmt = con.prepareStatement("INSERT INTO installments values(?,?,?,?,?)");
                stmt.setString(1, installments.getEgn());
                stmt.setInt(2, installments.getInsId());
                stmt.setInt(3, i + 1);
                stmt.setFloat(4, installments.getValues()[i]);
                stmt.setDate(5, new java.sql.Date(installments.getDates()[i].getTime()));
                stmt.execute();

                System.out.println("New installment added!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static Object[][] salesSearch(String company,String fromDate, String toDate) {
        if (company != null && fromDate != null && toDate != null ) {
            try {
                Statement stmt = DBConnection.getCon().createStatement();
                stmt.execute("SELECT i.policy, i.stickerNumber,i.numberOfPayments,"
                        + "i.dateIssued,i.validFrom,i.validTo,i.numberOfPayments, i.premia "
                        + "from Insurance i, Person p, "
                        + "Car c where i.carNumber = c.regnumber and c.owner_id = p.egn and "
                        + "i.company = '" + company + "'"
                        + "and i.dateIssued >= '"+ fromDate +"'and i.dateIssued <= '" + toDate + "';");

                return extractDatFromStatement(stmt);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return new Object[0][0];
    }

    public static Object[][] getDataByCompany(String company,String fromDate, String toDate) {
        if (company != null && fromDate != null && toDate != null ) {
            try {

                Statement stmt = DBConnection.getCon().createStatement();
                stmt.execute("select i.company,p.name,i.policy,i.validTo, i.premia "
                        + "from Insurance i, Person p, "
                        + "Car c where i.carNumber = c.regnumber and c.owner_id = p.egn and "
                        + "i.company = '" + company + "'"
                        + "and i.dateIssued >= '"+ fromDate +"'and i.dateIssued <= '" + toDate + "';");

                return extractDatFromStatement(stmt);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return new Object[0][0];
    }

    public static void exportToExcel(String filePath, Object[][] data, String[] columnNames) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet 1");

        int rownum = 0;
        Row row = sheet.createRow(rownum++);
        int cellnum = 0;
        for (String heading : columnNames) {
            Cell cell = row.createCell(cellnum++);
            cell.setCellValue(heading);
        }
        
        for (Object[] objArr : data) {
            row = sheet.createRow(rownum++);
            cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                if (obj instanceof Date) {
                    cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format((Date) obj));
                } else if (obj instanceof Boolean) {
                    cell.setCellValue((Boolean) obj);
                } else if (obj instanceof String) {
                    cell.setCellValue((String) obj);
                } else if (obj instanceof Float) {
                    cell.setCellValue((Float) obj);
                }
                else cell.setCellValue(obj.toString());
            }
        }

        try {
            try (FileOutputStream out = new FileOutputStream(new File(filePath))) {
                workbook.write(out);
            }
            System.out.println("Excel written successfully..");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Object[][] getInstallmentsForDriverWithEgn(String egn) {
        if (egn != null) {
            try {
                Statement stmt = DBConnection.getCon().createStatement();
                stmt.execute("SELECT number, value, date FROM installments where egn = '" + egn + "';");

                return extractDatFromStatement(stmt);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return new Object[0][0];
    }

    public static int getRowCount(ResultSet set) throws SQLException {
        int rowCount;
        int currentRow = set.getRow();            // Get current row  
        rowCount = set.last() ? set.getRow() : 0; // Determine number of rows  
        if (currentRow == 0) // If there was no current row  
        {
            set.beforeFirst();                     // We want next() to go to first row  
        } else // If there WAS a current row  
        {
            set.absolute(currentRow);              // Restore it  
        }
        return rowCount;
    }

    public static Object[][] getInsurancesFromLastXDays(int x) {
        if (x > 0) {
            try {
                Statement stmt = DBConnection.getCon().createStatement();
                stmt.execute("SELECT stickerNumber, policy, premia,"
                        + "FORMAT((0.02*premia),2), grajdanskiFond,"
                        + "FORMAT((premia+(0.02*premia)+grajdanskiFond),2) "
                        + "FROM insurance WHERE DATE_ADD(dateissued,INTERVAL " + x + " DAY) >= CURDATE();");

                return extractDatFromStatement(stmt);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return new Object[0][0];
    }

    public static Object[][] getLateInsurances() {
        try {
            Statement stmt = DBConnection.getCon().createStatement();
            stmt.execute("SELECT p.name,i.company,i.policy,i.validTo,i.premia, "
                    + "COALESCE(NULLIF(p.email,''), p.telephone) AS email, i.scannedDocument FROM insurance i, "
                    + "person p, car c WHERE (i.carNumber = c.regNumber) "
                    + "AND (c.driver_id = p.egn) AND (i.isDriverNotified=0) "
                    + "AND  (date_sub(date_add(i.dateIssued, interval 1 year), interval 14 day) <= CURDATE());");

            return extractDatFromStatement(stmt);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new Object[0][0];
    }

    private static Object[][] extractDatFromStatement(Statement stmt) throws SQLException {
        ResultSet rs = stmt.getResultSet();
        int cols = stmt.getResultSet().getMetaData().getColumnCount();
        Object[][] res = new Object[getRowCount(rs)][cols];
        while (rs.next()) {
            for (int i = 0; i < cols; i++) {
                res[rs.getRow() - 1][i] = rs.getObject(i + 1);
            }
        }
        return res;
    }

    public static void sendEmail(String to, Object attachment) {
        if (!emailSentHere(to)) {
            // Sender's email ID needs to be mentioned
            String from = "web@gmail.com";

            // Assuming you are sending email from localhost
            String host = "smtp.gmail.com";

            // Get system properties
            Properties properties = System.getProperties();

            // Setup mail server
            properties.setProperty("mail.smtp.host", host);
            properties.put("mail.smtp.starttls.enable", "true");
            properties.put("mail.smtp.auth", "true");
            javax.mail.Authenticator auth = new SMTPAuthenticator();
            Session session = Session.getDefaultInstance(properties, auth);

            try {
                // Create a default MimeMessage object.
                MimeMessage message = new MimeMessage(session);
                Multipart multipart = new MimeMultipart();

                MimeBodyPart messagePart = new MimeBodyPart();
                messagePart.setText("Вашата застраховка гражданска отговорност изтича!");
                multipart.addBodyPart(messagePart);

                if (attachment != null) {
                    MimeBodyPart attachmentPart = new MimeBodyPart();
                    ByteArrayDataSource attPic1 = new ByteArrayDataSource((byte[]) attachment, "image/jpeg");
                    attachmentPart.setDataHandler(new DataHandler(attPic1));
                    attachmentPart.setFileName(attPic1.getName());
                    multipart.addBodyPart(attachmentPart);
                }

                message.setContent(multipart);

                // Set From: header field of the header.
                message.setFrom(new InternetAddress(from));

                // Set To: header field of the header.
                message.addRecipient(Message.RecipientType.TO, new InternetAddress(
                        to));

                // Set Subject: header field
                message.setSubject("Информация за сключена застраховка гражданска отговорност");

                // Send message
                Transport.send(message);
                System.out.println("Sent message successfully...." + " to " + to);

                Statement stmt = DBConnection.getCon().createStatement();
                stmt.execute("SELECT id FROM insurance WHERE "
                        + "carNumber IN (SELECT regNumber FROM car WHERE driver_id = (SELECT egn FROM person WHERE "
                        + "email = '" + to + "'))");
                ResultSet rs = stmt.getResultSet();
                while (rs.next()) {
                    DBConnection.getCon().createStatement().execute("UPDATE insurance SET isDriverNotified = 1 WHERE id = " + rs.getInt(1));
                }
            } catch (MessagingException | SQLException mex) {
                mex.printStackTrace();
            }
        }
    }

    private static boolean emailSentHere(String to) {
        Statement stmt;
        try {
            stmt = DBConnection.getCon().createStatement();
            stmt.execute("SELECT isDriverNotified FROM insurance WHERE id IN (SELECT id FROM insurance WHERE "
                    + "carNumber IN (SELECT regNumber FROM car WHERE driver_id IN (SELECT egn FROM person WHERE "
                    + "email = '" + to + "')))");
            ResultSet rs = stmt.getResultSet();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }


        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;


    }

    /**
     * SimpleAuthenticator is used to do simple authentication when the SMTP
     * server requires it.
     */
    private static class SMTPAuthenticator extends javax.mail.Authenticator {

        public PasswordAuthentication getPasswordAuthentication() {
            String username = "avesvaldas@gmail.com";
            String password = "76v73e76";
            return new PasswordAuthentication(username, password);
        }
    }
}
